import pymysql

# 建立到数据库的连接
conn = pymysql.connect(
    host='127.0.0.1', port=3306,
    user='root', password='tedu.cn',
    db='nsd1910', charset='utf8'
)

# 创建游标，游标相当于是文件对象，通过文件对象可以对文件进行读写，通过游标可以对数据库进行增删改查
cur = conn.cursor()

# 编写sql语句
create_dep = '''CREATE TABLE departments(
dep_id INT, dep_name VARCHAR (20),
PRIMARY KEY (dep_id)
)'''
create_emp = '''CREATE TABLE employees(
emp_id INT, emp_name VARCHAR (20), email VARCHAR (50), dep_id INT,
PRIMARY KEY (emp_id), FOREIGN KEY (dep_id) REFERENCES departments(dep_id)
)'''
create_sal = '''CREATE TABLE salary(
id INT, date DATE, emp_id INT, basic INT, awards INT,
PRIMARY KEY (id), FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
)'''

# 通过游标执行sql语句
cur.execute(create_dep)
cur.execute(create_emp)
cur.execute(create_sal)

# 关闭
cur.close()
conn.close()
